package com.ycy;

import cn.hutool.core.codec.Base64;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;

@Slf4j
public class ExcelReadByteDemo {

    /**
     *
     *   base-> 字节
     *
     * 后端
     *   字节-> Workbook
     *   修改 Workbook
     *   Workbook -> 字节
     *   字节 -> 对象存储
     *
     * base64 生成xlsx文件
     * @param args
     */
    public static void main1(String[] args) {
        // 文件路径
        String filePath = "C:\\Users\\yincaiyong\\Desktop\\居住区.txt";

        // 桌面路径
        String desktopPath = System.getProperty("user.home") + "/Desktop/decodedFile.xlsx";

        try {
            // 读取文件内容
            String base64Content = null;

            base64Content = new String(Files.readAllBytes(Paths.get(filePath)));

            // 解码Base64内容
//            byte[] decodedBytes = Base64.getDecoder().decode(base64Content);

            byte[] decodedBytes = Base64.decode(base64Content);

            // 移除数据
            ByteArrayInputStream bais = new ByteArrayInputStream(decodedBytes);


            // 将字节数组写入到文件
            try (FileOutputStream fos = new FileOutputStream(desktopPath)) {
                fos.write(decodedBytes);
                System.out.println("File saved to: " + desktopPath);
            }


            // 处理解码后的字节数组
            // 例如，将其转换为字符串
//            String originalContent = new String(decodedBytes);
//            System.out.println("Decoded content: " + originalContent);

        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }

    public static void main(String[] args) {

        // 桌面路径
        String filePath = System.getProperty("user.home") + "/Desktop/居住区（居民小区）台账导入模板base64.txt";

        try {
            // 读取文件内容
            String base64Content = null;

            base64Content = new String(Files.readAllBytes(Paths.get(filePath)));

            byte[] decodedBytes = Base64.decode(base64Content);

            // 移除数据
            ByteArrayInputStream bais = new ByteArrayInputStream(decodedBytes);

            try (
                 Workbook workbook = new XSSFWorkbook(bais)) {
                // 获取第一个Sheet
                Sheet sheet = workbook.getSheetAt(0);

                // 创建单元格样式
                CellStyle unlockedCellStyle = workbook.createCellStyle();
                unlockedCellStyle.setLocked(false); // 设置单元格为未锁定

                // 获取最后一行的行号
                int lastRowNum = sheet.getLastRowNum();
                // 从第五行开始删除所有记录
                for (int i = 4; i <= lastRowNum; i++) {
                    Row row = sheet.getRow(i);
                    if (row != null) {
                        sheet.removeRow(row);
                    }
                }

                // 第五行写一条数据
                Row row = sheet.createRow(4);
                Cell cell = row.createCell(8);

                cell.setCellValue("hello world"); // 假设所有数据都可以转换为字符串
                cell.setCellStyle(unlockedCellStyle);

                // 将修改后的工作簿写回文件
                try (FileOutputStream fos = new FileOutputStream(System.getProperty("user.home") + "/Desktop/decodeFile1_"+System.currentTimeMillis()+".xlsx")) {
                    workbook.write(fos);
                }

                replaceSheet(workbook.getNumberOfSheets(),workbook);

                // 第五行写一条数据
                Row row4 = sheet.createRow(4);
                Cell cell9 = row.createCell(9);

                cell9.setCellValue("hello world9999999999"); // 假设所有数据都可以转换为字符串
                cell9.setCellStyle(unlockedCellStyle);

                // 将修改后的工作簿写回文件
                try (FileOutputStream fos = new FileOutputStream(System.getProperty("user.home") + "/Desktop/decodeFile2_"+System.currentTimeMillis()+".xlsx")) {
                    workbook.write(fos);
                }

                //读取 行数总数
                int lastRowNum1 = sheet.getLastRowNum();
                log.info("lastRowNum -> {}" , lastRowNum1);

                System.out.println("Rows deleted successfully!");
            } catch (IOException e) {
                e.printStackTrace();
            }

//            // 将字节数组写入到文件
//            try (FileOutputStream fos = new FileOutputStream(System.getProperty("user.home") + "/Desktop\\decodeFile1.xlsx")) {
//                fos.write(decodedBytes);
//                System.out.println("File saved to: " + desktopPath);
//            }

        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public static final int DATA_ROW_IDX = 3;

    private static void replaceSheet(int numberOfSheets, Workbook workbook) {
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet oldSheet = workbook.getSheetAt(i);
            String sheetName = oldSheet.getSheetName(); // 获取旧的Sheet的名称
            Sheet newSheet = workbook.createSheet(); // 创建新的Sheet

            // 复制行
            for (int j = 0; j <= DATA_ROW_IDX; j++) {
                Row oldRow = oldSheet.getRow(j);
                if (oldRow != null) {
                    Row newRow = newSheet.createRow(j);
                    newRow.setHeight(oldRow.getHeight());
                    for (int k = oldRow.getFirstCellNum(); k < oldRow.getLastCellNum(); k++) {
                        Cell oldCell = oldRow.getCell(k);
                        if (oldCell != null) {
                            Cell newCell = newRow.createCell(k);
                            newCell.setCellStyle(oldCell.getCellStyle());
                            switch (oldCell.getCellType()) {
                                case STRING:
                                    newCell.setCellValue(oldCell.getStringCellValue());
                                    newCell.setCellType(oldCell.getCellType());
                                    break;
                                case NUMERIC:
                                    newCell.setCellValue(oldCell.getNumericCellValue());
                                    newCell.setCellType(oldCell.getCellType());
                                    break;
                                case BOOLEAN:
                                    newCell.setCellValue(oldCell.getBooleanCellValue());
                                    newCell.setCellType(oldCell.getCellType());
                                    break;
                                case FORMULA:
                                    newCell.setCellFormula(oldCell.getCellFormula());
                                    newCell.setCellType(oldCell.getCellType());
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                }
            }

            // 复制合并单元格
            for (int j = 0; j < oldSheet.getNumMergedRegions(); j++) {
                CellRangeAddress cellRangeAddress = oldSheet.getMergedRegion(j);
                if (cellRangeAddress.getFirstRow() <= DATA_ROW_IDX) {
                    newSheet.addMergedRegion(cellRangeAddress);
                }
            }

            int oldSheetIndex = workbook.getSheetIndex(oldSheet);
            workbook.removeSheetAt(oldSheetIndex); // 删除旧的Sheet
            workbook.setSheetName(oldSheetIndex, sheetName); // 将新的Sheet的名称设置为旧的Sheet的名称
        }
    }


}
